package com.hxx.two.jdbc.action;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import com.hxx.two.jdbc.domain.TUser;

public class Test {
    //获取数据库链接
	Connection conn = DbManager.getInstance().getConnection();  
	//创建查询对象  
    QueryRunner queryRunner = new QueryRunner(); 
    
    public static void main(String[] args) {
		//new Test().selectOne();
    	//new Test().selectAll();
    	//new Test().selectOneObject();
    	//new Test().selectOneObjectAll();
    	new Test().saveOne();
        //new Test().delete();
    	new Test().update();
	}
    //更新数据
    public void update(){
    	try {
			queryRunner.update(conn, "update t_user set username=?,sex=?,pwd=? where id=?", 
					          new String[]{"zhangsan","gril","555","12"});
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
    
    //删除数据
    public void delete(){
    	try {
			queryRunner.update(conn, "delete from t_user where id=?", 11);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
    
    //添加数据
    public void saveOne(){
    	try {
			queryRunner.update(conn, "insert into t_user (username,sex,pwd) values (?,?,?)", 
					           new String[]{"zmx","boy","12345"});
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
    
    //处理多行为对象
    public void selectOneObjectAll(){
    	try {
			List<TUser> lst = (List<TUser>) queryRunner.query(conn,"select * from t_user",
					new BeanListHandler(TUser.class));
			for(TUser user:lst){
				System.out.println(user.getUsername());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
    
    //处理单行为对象
    public void selectOneObject(){
    	try {
			TUser user = (TUser) queryRunner.query(conn,"select * from t_user",
					new BeanHandler(TUser.class));
			System.out.println(user.getUsername());
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
    
    //获取多行数据
    public void selectAll(){
    	try {
			List<Map<String,Object>> lst = (List<Map<String, Object>>) queryRunner
						.query(conn,"select * from t_user",new MapListHandler(),(Object[])null);
			Iterator<Map<String,Object>> it = lst.iterator();
			while(it.hasNext()){
				Iterator<Entry<String, Object>> it1 =  it.next().entrySet().iterator();
				while(it1.hasNext()){
					Entry e = it1.next();
					System.out.println(e.getKey()+"|"+e.getValue());
				}
				System.out.println("------------------------------------------------");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
    
    //获取单行数据
    public void selectOne(){
    	try {
    		//MapHandler ：将ResultSet中第一行的数据存成Map映射
			Map<String,Object> map = (Map<String, Object>) queryRunner
									.query(conn,"select * from t_user",new MapHandler(),(Object[])null);

			Set<Entry<String, Object>> set = map.entrySet();
			Iterator<Entry<String, Object>> it = set.iterator();
			while(it.hasNext()){
				Entry e = it.next();
				System.out.println(e.getKey()+"|"+e.getValue());
			}
    	} catch (SQLException e) {
			e.printStackTrace();
		}
    }
    
}







































































